USE [CASASHOW]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP07_CADASTRO_INGRESSO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP07_CADASTRO_INGRESSO]
GO

/****** Object:  StoredProcedure [dbo].[SP07_CADASTRO_INGRESSO]    Script Date: 11/19/2013 12:02:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP07_CADASTRO_INGRESSO]
       @XML XML
AS
/******      Declaracao das variaveis locais - NUNCA utilize os parametros de entrada nos comandos SQL 
*/

DECLARE @V_XML XML
SET @V_XML = @XML

/******      Crie a logica da procedure abaixo
*/


SELECT         
	doc.col.value('ID_INGRESSO[1]', 'INT') AS ID_INGRESSO,    
	doc.col.value('ID_SHOW[1]','INT') AS ID_SHOW,
	doc.col.value('IC_INGRESSO_DISPONIVEL[1]','BIT') AS IC_INGRESSO_DISPONIVEL,    
	doc.col.value('ACAO[1]','VARCHAR(45)') AS ACAO INTO #TEMP_INGRESSO   
FROM @V_XML.nodes('ArrayOfIngresso/Ingresso') doc(col)

IF EXISTS(SELECT TOP 1 1 FROM #TEMP_INGRESSO WITH(NOLOCK) WHERE UPPER(ACAO) = 'INCLUIR')    
BEGIN
INSERT INTO TB05_INGRESSO
			(ID_INGRESSO,
			 ID_SHOW,
			 IC_INGRESSO_DISPONIVEL)
			SELECT TEMP.ID_INGRESSO,
				TEMP.ID_SHOW,
				TEMP.IC_INGRESSO_DISPONIVEL
		FROM #TEMP_INGRESSO TEMP WITH(NOLOCK) WHERE UPPER(TEMP.ACAO) = 'INCLUIR'
END

IF EXISTS(SELECT TOP 1 1 FROM #TEMP_INGRESSO WITH(NOLOCK) WHERE UPPER(ACAO) = 'EXCLUIR')    
BEGIN
UPDATE TB05
SET TB05.IC_INGRESSO_DISPONIVEL = 0   
FROM #TEMP_INGRESSO TEMP
INNER JOIN TB05_INGRESSO TB05 WITH(NOLOCK) ON TB05.ID_INGRESSO = TEMP.ID_INGRESSO
WHERE UPPER(TEMP.ACAO) = 'EXCLUIR'
END

IF EXISTS(SELECT TOP 1 1 FROM #TEMP_INGRESSO WITH(NOLOCK) WHERE UPPER(ACAO) = 'ALTERAR')    
BEGIN    
UPDATE TB05
SET TB05.ID_SHOW = ISNULL(TEMP.ID_SHOW,TB05.ID_SHOW),
TB05.IC_INGRESSO_DISPONIVEL = ISNULL(TEMP.IC_INGRESSO_DISPONIVEL,TB03.IC_INGRESSO_DISPONIVEL)
FROM #TEMP_INGRESSO TEMP
INNER JOIN TB05_INGRESSO TB05 WITH(NOLOCK) ON TB05.ID_INGRESSO = TEMP.ID_INGRESSO
WHERE UPPER(TEMP.ACAO) = 'ALTERAR'

END

DROP TABLE #TEMP_INGRESSO
